package com.is.control;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import com.is.dbconn.DBConnect;
import com.is.model.AttendanceDetail;

public class AttendanceDetailControl {
	
	public static boolean insertAttendance(AttendanceDetail attd){
		DBConnect dbConnect = new DBConnect();
		String sql = "insert into attendance_detail (stuId,reasonType,timeId,stuyear) values(?,?,?,?)";
		dbConnect.prepareStatement(sql);
		try {
			setValue(attd, dbConnect);
			if(dbConnect.executeUpdate()){
				return true;
			}
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("insertAtt ERROR");
		}finally{
			dbConnect.close();
		}
		return false;
	}
	
	
	public static boolean insertAttendance(List<AttendanceDetail> atts){
		DBConnect dbConnect = new DBConnect();
		String sql = "insert into attendance_detail (stuId,reasonType,timeId,stuyear) values(?,?,?,?)";
		PreparedStatement pstmt = null;
		dbConnect.setAutoCommit(false);
	    dbConnect.prepareStatement(sql);
	    pstmt = dbConnect.getPreparedStatement();
	    try {
			for(AttendanceDetail ad :atts ){
				int index = 1;
				pstmt.setInt(index++, ad.getStuId());
				pstmt.setInt(index++,ad.getReasonType());
				pstmt.setString(index++, ad.getTimeId());
				pstmt.setInt(index++, ad.getStuyear());
				pstmt.addBatch();
			}
			pstmt.executeBatch();
			dbConnect.commit();
			dbConnect.setAutoCommit(true);
			return true;
		} catch (Exception e) {
			dbConnect.rollback();
			e.printStackTrace();
			System.out.println("insertAttendance(List) ERROR");
			return false;
		}finally{
			dbConnect.close();
		}
	} 
	
	public static boolean deleteAttendanceById(int id){
		DBConnect dbConnect = new DBConnect();
		String sql= "delete from attendance_detail where id = '"+id+"' ";
		dbConnect.prepareStatement(sql);
		try {
			if(dbConnect.executeUpdate()){
				return true;
			}
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("deleteAtt ERROR");
		}finally{
			dbConnect.close();
		}
		return false;
	}
	
	public static boolean updateReasonTypeById(int id,int reasonType){
		DBConnect dbc= new DBConnect();
		String sql = "update attendance_detail set reasonType = '"+id+"' where id = '"+id+"'";
		dbc.prepareStatement(sql);
		try {
			if(dbc.executeUpdate()){
				return true;
			}
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("updateReasonTypeByid Error");
		}finally{
			dbc.close();
		}
		return false;
	}
	
	public static int getReasonTypeByStuIdAndTimeId(int stuId,int timeId) {
	    DBConnect dbConnect = new DBConnect();
	    String sql = "select reasonType from attendance_detail where stuId='"+stuId+"' and timeId='"+timeId+"'";
	    ResultSet rSet = null;
	    int type=0;
	    dbConnect.prepareStatement(sql);
	    try {
		rSet = dbConnect.executeQuery();
		if(rSet.next()){
		    type = rSet.getInt("reasonType");
		}
	    } catch (Exception e) {
		e.printStackTrace();
		System.out.println("getAttendanceDetailByStuIdAndTimeId ERROR");
	    }finally{
		dbConnect.close();
	    }
	    return type;
	}
	public static boolean updateReasonTypeByIdAndTimeId(int stuId,int timeId,int type){
		DBConnect dbc = new DBConnect();
		String sql = "update attendance_detail set reasonType = "+ type +" where stuId='"+stuId+"' and timeId='"+timeId+"'";
		dbc.prepareStatement(sql);
		try{
			if(dbc.executeUpdate()){
				return true;
			}
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("updateReasonTypeByIdAndTimeId ERROR");
		} finally{
			dbc.close();
		}
		return false;
	}
	public static void setValue(AttendanceDetail att,DBConnect dbc){
		//调用此方法需注意字段顺序(与sql语句保持一致)
		int index=1;
		try{
		dbc.setInt(index++, att.getStuId());
		dbc.setInt(index++, att.getReasonType());
		dbc.setString(index++, att.getTimeId());
		dbc.setInt(index++, att.getStuyear());
	}catch (Exception e) {
		e.printStackTrace();
		System.out.println("setValue方法出错");
	}
	}
	
	public static void getRs(AttendanceDetail a ,ResultSet rs){
		try {
			a.setId(rs.getInt("id"));
			a.setStuId(rs.getInt("stuId"));
			a.setReasonType(rs.getInt("reasonType"));
			a.setTimeId(rs.getString("timeId"));
			a.setStuyear(rs.getInt("stuyear"));
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("getRs出错");
		}
	}
	
	//根据时间以及课程编号删除某次点名情况
	public static boolean deleteAttendaceByTimeAndCourseId(String time,int courseClassId){
		DBConnect dbConnect = new DBConnect();
		String sql = "delete from attendance_detail where courseClassId = '"+courseClassId+"' and timeId = '"+time+"'";
		dbConnect.prepareStatement(sql);
		try {
			if(dbConnect.executeUpdate()){
				return true;
			}
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("deleteAttendaceByTimeAndCourseId ERROR");
		}finally{
			dbConnect.close();
		}
		return false;
	}
	
	public static ArrayList<AttendanceDetail> getAttendanceDetailByTimeId(int timeId) {
		ArrayList<AttendanceDetail> ads = new ArrayList<AttendanceDetail>();
		DBConnect dbConnect = new DBConnect();
		String sql = "select * from attendance_detail where timeId='"+timeId+"'";
		dbConnect.prepareStatement(sql);
		ResultSet rSet=null;
		try {
			rSet = dbConnect.executeQuery();
			while(rSet.next()){
			    AttendanceDetail ad = new AttendanceDetail();
				getRs(ad, rSet);
				ads.add(ad);
			}
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("getAttendanceDetailByTimeId ERROR");
		}finally{
			dbConnect.close();
		}
		return ads;
	}
	
	/**
	 * 
	 * 统计一个学生在某个课程出勤情况（缺省情况下为所有的课程之和）
	 */
	public static int getCount(int reasonType,int stuId,String courseClassId,int stuyear,String time1,String time2){
		DBConnect dbConnect = new DBConnect();
		String sTime = ParamStuyearControl.getStuyear(stuyear).getStartTime();
		if(time1!=null&&!time1.equals("")){
			sTime = time1;
		}
		String eTime = ParamStuyearControl.getStuyear(stuyear).getEndTime();
		if(time2!=null&&!time2.equals("")){
			eTime = time2;
		}
		if (courseClassId==null||courseClassId.equals("")) {
			courseClassId="%";
		}
		String sql = "select count(attendance_detail.id) from attendance_detail,attendance_time where reasonType = '"+reasonType+"' and stuId = '"+stuId+"' and " +
				"timeId  =attendance_time.id and  courseClassId = '"+courseClassId+"' and  (time between '"+sTime+"' and '"+eTime+"')";
		dbConnect.prepareStatement(sql);
		//System.out.println(sql);
		ResultSet rs = null;
		try {
			rs = dbConnect.executeQuery();
			if(rs.next()){
				return rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("getCount Error");
		}finally{
			dbConnect.close();
		}
		return 0;
	}
	
	/**
	 * 单个学生在某课程的折合缺勤次数
	 * @return
	 */
	public static String getTotalQQByStuIdAndCourseClassIdAndStuyearId(int stuId,int courseClassId,int usingStuyear) {
		  String result = "公式有误";
		  float totalQQ=0;
		  boolean flag = true;
         int count2 = AttendanceDetailControl.getCount(2,stuId,courseClassId+"",usingStuyear,"","");
         int num2 = ParamReasonTypeGSControl.getNumByTypeAndStuyear(2,usingStuyear);
         if(num2==0){
       	      flag = false;
         }else{
       	      totalQQ+= (float)count2/num2;
         }
         int count3 = AttendanceDetailControl.getCount(3,stuId,courseClassId+"",usingStuyear,"","");
         int num3 = ParamReasonTypeGSControl.getNumByTypeAndStuyear(3,usingStuyear);
         if(num3==0){
       	      flag = false;
         }else{
       	     totalQQ+= (float)count3/num3;
         }
         int count4 = AttendanceDetailControl.getCount(4,stuId,courseClassId+"",usingStuyear,"","");
         int num4 = ParamReasonTypeGSControl.getNumByTypeAndStuyear(4,usingStuyear);
         if(num4==0){
       	       flag = false;
         }else{
       	      totalQQ+= (float)count4/num4;
         }
         int count5 = AttendanceDetailControl.getCount(5,stuId,courseClassId+"",usingStuyear,"","");
         int num5 = ParamReasonTypeGSControl.getNumByTypeAndStuyear(5,usingStuyear);
         if(num5==0){
       	      flag = false;
         }else{
       	      totalQQ+= (float)count5/num5;
         }
         if(flag){
        	 String temp = totalQQ+"";
        	 result =temp.length()>5?temp.substring(0,5):temp;
         }
         return result;
	}
	
	public static void main(String args[]) {
		System.out.println(getReasonTypeByStuIdAndTimeId(76,28));
	}

}
